package stanaGUI;


/*
 * Browsing DB .java requires no other files.
 */

import javax.swing.*;
import javax.swing.tree.*;
import javax.swing.event.*;

import com.mysql.jdbc.Statement;

import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBBrowsingOp {

	DBBrowsingArea dbBrowsingArea;
    final static String newline = "\n";
    static String DBNameP = "";
    String fields[];
    String tables[];
    JTable DataTable = null;
	static JPanel test1;
	static JTabbedPane pane;
	static JFrame mainF = null;
	static Connection con;
	
	public DBBrowsingOp(final MainWindow mainT,Connection con, 
			String DBName,
			String userName,
			String password,
			String serverName) {
		
		mainT.setTitle("DB Browsing");
		mainT.setBounds(50,50,1000,1000);

		GridBagLayout gridbag = new GridBagLayout();
		GridBagConstraints gbc = new GridBagConstraints();
		mainT.setLayout(gridbag);
		this.con = con;
		this.DBNameP = DBName;
		dbBrowsingArea = new DBBrowsingArea(con, DBNameP,userName, password, serverName);
		JPanel testPanel = new JPanel();
		testPanel.add(dbBrowsingArea);
		gbc.fill = GridBagConstraints.HORIZONTAL;
		gbc.gridy = 0;
		gbc.gridx = 0;
		mainT.add(dbBrowsingArea, gbc);
		
		pane = new JTabbedPane();
		pane.setLocation(440,5);
		pane.setPreferredSize(new Dimension(570,500));
		test1 = new JPanel();
		// JTable to add after the Query
		if (DataTable != null ) {
			test1.add(DataTable);
			pane.add(test1);
		}
		gbc.gridx = 1;
		gbc.gridy = 0;
		mainT.add(pane, gbc);
		
		gbc.gridx = 1;
		gbc.gridy = 2;
		JLabel gap = new JLabel();
		mainT.add(gap,gbc);

		JButton submit = new JButton("submit");
		gbc.fill = GridBagConstraints.CENTER;
		gbc.gridx = 1;
		gbc.gridy = 3;
		submit.setLocation(440, 550);
		submit.setSize(100, 30);
		mainT.add(submit,gbc);
		submit.addActionListener(new ActionListener() {

			@Override
			public void actionPerformed(ActionEvent arg0) {
				// TODO Auto-generated method stub
				mainT.stanaAlgorithm();

			}
			
		});
		mainF = mainT;
		mainT.setVisible(true);
		
	}


    class DBBrowsingArea extends JScrollPane
                   implements TreeExpansionListener,
                   			  TreeSelectionListener {
    	
        Dimension minSize = new Dimension(250, 500);
        JTree tree;
        Connection con;
        String DBNameP = "";
        String userName = "";
        String password = "";
        String serverName = "";
    	String sqlQuery = "";
    	String[] tableNames;
    	String[] databaseNames;
    	String[] fieldNames;

    	int numOfDatabases = 0;
    	int numOfTables = 0;
    	int numOfFields = 0;
        public DBBrowsingArea(Connection con, 
        		String DBName,
        		String userName, 
        		String password, 
        		String serverName) {
        	if (con == null) {
        		getConnection();
        	} else {
        		this.con = con;
        	}
        	this.DBNameP = DBName;
        	this.userName = userName;
        	this.password = password;
        	this.serverName = serverName;
        	this.setLocation(10,10);
        	this.setPreferredSize(new Dimension(370,500));
            TreeNode rootNode = createNodes();
            tree = new JTree(rootNode);
            tree.addTreeExpansionListener(this);
            tree.addTreeSelectionListener(this);
            tree.getSelectionModel().setSelectionMode(TreeSelectionModel.DISCONTIGUOUS_TREE_SELECTION);
            setViewportView(tree);
        }

    	public void getConnection() {
    		try		{
    			Class.forName("com.mysql.jdbc.Driver").newInstance();
    			String conServer = "jdbc:mysql://" + serverName + "/" + this.DBNameP;
    			con = DriverManager.getConnection(
    					conServer, userName, password);
    			this.con= con;
    		} catch (Exception e) 	{
    			System.err.println ("Cannot connect to database server");
    		} finally {
    			
    		}
    	}
    	// To close the connection
    	public void closeConnection() {
    		try {
    			con.close();
    		} catch(Exception e){
    			e.printStackTrace();
    		}
    	}
        
        public void getDatabaseNames() {
    		getConnection();
    		try {
    			Statement stat = (Statement) con.createStatement();
    			sqlQuery = "show databases";
    			ResultSet rst=stat.executeQuery(sqlQuery);
    			rst=stat.executeQuery(sqlQuery);
    			rst.last();
    			numOfDatabases = rst.getRow();
    			rst.beforeFirst();
    			databaseNames = new String[numOfDatabases];
    			int i = 0;
    			while(rst.next())
    			{
    				databaseNames[i] = rst.getString(1);
    				i++;
    			}
    		} catch (SQLException e) {
    			System.out.println(" the Stack trace " + e.getMessage());
    		}
    		closeConnection();
        }
        public void getTableNames(String DBName) {
    		getConnection();
    		String sqlQuery = "use " + DBName;
    		try {
    			Statement stat = (Statement) con.createStatement();
    			ResultSet rst=stat.executeQuery(sqlQuery);
    			sqlQuery = "show tables";
    			rst=stat.executeQuery(sqlQuery);
    			rst.last();
    			numOfTables = rst.getRow();
    			rst.beforeFirst();
    			tableNames = new String[numOfTables];
    			int i = 0;
    			while(rst.next())
    			{
    				tableNames[i] = rst.getString(1);
    				i++;
    			}
    		} catch (SQLException e) {
    			System.out.println(" the Stack trace " + e.getMessage());
    		}
    		closeConnection();

        }
        public void getFieldNames(String DBName, String tableName) {
    		getConnection();
    		String sqlQuery = "use " + DBName;
    		try {
    			Statement stat = (Statement) con.createStatement();
    			ResultSet rst=stat.executeQuery(sqlQuery);
    			sqlQuery = "describe " + tableName;
    			rst=stat.executeQuery(sqlQuery);
    			rst.last();
    			numOfFields = rst.getRow();
    			rst.beforeFirst();
    			fieldNames = new String[numOfFields];
    			int i = 0;
    			while(rst.next())
    			{
    				fieldNames[i] = rst.getString(1);
    				i++;
    			}
    		} catch (SQLException e) {
    			System.out.println(" the Stack trace " + e.getMessage());
    		}
    		closeConnection();
        }

        private TreeNode createNodes() {
        	
            DefaultMutableTreeNode root;
            DefaultMutableTreeNode grandparent;
            DefaultMutableTreeNode parent;
            DefaultMutableTreeNode child;
            root = new DefaultMutableTreeNode("Database List");
            getDatabaseNames();
        	grandparent = new DefaultMutableTreeNode(databaseNames[0]);
            
            for (int i = 0; i < databaseNames.length; i++) {
            	
            	grandparent = new DefaultMutableTreeNode(databaseNames[i]);
            	root.add(grandparent);
            	// Now call for the number of fields in the table
                getTableNames(databaseNames[i]);
                for (int j = 0; j < numOfTables; j++ ) {
                    parent = new DefaultMutableTreeNode(tableNames[j]);
                    grandparent.add(parent);
                    // Now add the child nodes as the fields. and allow multi selection
                    getFieldNames(databaseNames[i], tableNames[j]);
                    for (int k = 0; k < numOfFields; k++) {
                    	child = new DefaultMutableTreeNode(fieldNames[k]);
                        parent.add(child);
                    }
                }
            }
            
            return root;
        }
    
        public Dimension getMinimumSize() {
            return minSize;
        }

        public Dimension getPreferredSize() {
            return minSize;
        }

		@Override
		public void treeCollapsed(TreeExpansionEvent arg0) {
			// TODO Auto-generated method stub
			
		}

		@Override
		public void treeExpanded(TreeExpansionEvent arg0) {
			// TODO Auto-generated method stub
			
		}

		@Override
		public void valueChanged(TreeSelectionEvent e) {
			TreePath[] paths = tree.getSelectionPaths();
			if (paths.length >= 1) {
				fields = new String[paths.length];
				tables = new String[paths.length];
				int numOfFields = paths.length;
				String sqlQuery = " select ";
					for (int i = 0; i < paths.length; i++) {
						fields[i] =  paths[i].getLastPathComponent().toString();
						tables[i] = paths[i].getPathComponent(2).toString();
						if (i == paths.length - 1)
							sqlQuery += tables[i] + "." + fields[i];
						else 
							sqlQuery += tables[i] + "." + fields[i] + ",";
					}
			String tablesList = "";
			for (int i = 0; i < paths.length; i++) {
				if (i == 0)
					tablesList += tables[i];
				if ((i > 0) && (i < paths.length)) {
					if (tablesList.contains(tables[i])) {
						continue;
					} else {
						if ((i > 0) && (i < paths.length)) {
							tablesList += "," + tables[i];
						}
					}
				}
			sqlQuery += " from " +  tablesList;
			String[][] data = null ;
				
	  		try {
	  			getConnection();
    			Statement stat = (Statement) con.createStatement();
    			String query = "use " + DBNameP;
    			stat.execute(query);
    			ResultSet rst=stat.executeQuery(sqlQuery);
    			rst.last();
    			int returnedRec = rst.getRow();
    			data = new String[returnedRec][numOfFields];
    			rst.beforeFirst();
    			int j = 0;
    			while (rst.next()) {
    					for (i = 0; i < paths.length; i++) {
    						data[j][i] = rst.getString(i+1);
    					}
    					j++;
    				}
	  		} catch (SQLException sqlExcept) {
	  			System.out.println(" the sql query " + sqlQuery + " error");
	  			System.out.println(sqlExcept.getMessage());
	  		}
	  		// Create the JTable
	  		DataTable = new JTable(data,fields);
	  		
	  		JPanel test1 = new JPanel();
	  		test1.setLayout(new BorderLayout()); 

	  		if (DataTable != null ) {
				test1.add(DataTable.getTableHeader(), BorderLayout.NORTH);
				test1.add(DataTable, BorderLayout.CENTER);
				GridBagLayout gridbag = new GridBagLayout();
				GridBagConstraints gbc = new GridBagConstraints();
				gbc.fill = GridBagConstraints.HORIZONTAL;
				gbc.gridy = 0;
				gbc.gridx = 1;
				if (test1 != null) {
					pane.removeAll();
					pane.add(test1);
					mainF.add(pane);
				}
			}
			}	
			}
		}
    }
	/**
     * Create the GUI and show it.  For thread safety,
     * this method should be invoked from the
     * event-dispatching thread.
     */

    public static void main(String[] args) {
        //Schedule a job for the event-dispatching thread:
        //creating and showing this application's GUI.
        javax.swing.SwingUtilities.invokeLater(new Runnable() {
            public void run() {
            	MainWindow mainT = new MainWindow();
            	new DBBrowsingOp(mainT,null, 
            			"supermarket",
            			"root",
            			"root",
            			"localhost");
            }
        });
    }
}
